package com.wuyou.util;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.fastjson.JSON;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.util.List;

/**
 * 读取excel文件为数据集合，然后根据sqlformat写入到txt文件
 * @author tail7
 */
public class ExcelToJsonAndToText {


    private static final Logger log = LoggerFactory.getLogger(ExcelToJsonAndToText.class);

    public static class User {
        private String name;
        private Integer age;

        // getter 和 setter 方法
    }


    public static void main(String[] args) {

        // Excel 文件路径
        String filePath = "C:/Users/tail7/Desktop/1.xlsx";

        // 读取excel Workbook
        File bookFile = new File(filePath);

        if (!bookFile.exists()) {
            log.info("为查询到文件");
        }
        ExcelReader excelReader = ExcelUtil.getReader(bookFile, "Sheet1");
        List<List<Object>> read = excelReader.read();
        System.out.println(JSON.toJSONString(read));

        StringBuilder sb = new StringBuilder();
        for (List<Object> objects : read) {
            String sql = "INSERT INTO mpaas_task.ts_task_item (task_no,duty_code,duty_person,tenant_id,created_bp,created_by,created_date,created_name,is_active,updated_by,updated_date,updated_name,item_task_type,item_task_status,item_task_no,extend1,extend2,extend3,extend4,extend5) VALUES" +
                    " ('{}',NULL,NULL,'1668495802865164288',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'{}','','{}', NULL,NULL,NULL,NULL);";
            Object[] array = objects.toArray();
            array[2] = "00" +  array[2];
            String temp = array[2].toString();
            array[2] = array[1];
            array[1] = temp;

            String format = StrUtil.format(sql, array);
            if (array[2] != null) {
                String x = format + "\r\n";
                sb.append(x);
                System.out.println(x);
                // 将打印信息写入 C:/Users/tail7/Desktop/2.txt 如果没文件就创建
            }
        }
        FileUtil.writeString(sb.toString(), "C:/Users/tail7/Desktop/2.txt", "UTF-8");



    }











}
